/*
 * 作者：姚俊
 * 功能：获奖的逻辑处理
 * 日期：2015-05-29
 */
package admin.service.fruits;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import beanDao.MapToBean;

import teacher.bean.Award;
import teacher.bean.Teacher;
import tools.StringDate;
import database.dao.MysqlCrud;

public class AwardService {
	
	
	
	/**
	 * @category 根据获奖的ID来查询一个获奖
	 * @author yaojun
	 * @param Award Award 传入一个获奖信息的对象，该对象必须有获奖信息的ID
	 * @return Award 返回的是一个获奖信息的对象
	 */
	public Award getAward(Award award)
	{
		Award awa =new Award();
		String sql="select * from Award where awaId=?";
		String[]  parameter={String.valueOf(award.getAwaId())};
        MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		if(lists.size()>0&&lists!=null)
		{	
			Map map=lists.get(0);
			awa=new MapToBean().MapToAward(map);
		}
		
		return awa;
	}	
	
	/**
	 * @category 根据获奖信息的ID来修改这个获奖的信息
	 * @author yaojun
	 * @param inn1 Award 传入一个要用于修改的Award对象
	 * @param inn2 Award 传入一个根据这个Award对象的ID来查找需要修改的哪一条获奖信息数据
	 * @return boolean 成功则返回true
	 */
	public boolean updateAward(Award award1,Award award2)
	{
		boolean b=false;
		String sql="update Award set name=?,projectName=?," +
		"level=?,people=?,date=?,number=?,upload=?,teaId=?,state=?," +
		"kind=?,unitNum=?,empowerUnit=? where awaId=?";
		
		String[] parameter={
				String.valueOf(award1.getName()),
				String.valueOf(award1.getProjectName()),
				String.valueOf(award1.getLevel()),
				String.valueOf(award1.getPeople()),
				String.valueOf(StringDate.DateToString(award1.getDate())),
				String.valueOf(award1.getNumber()),
				String.valueOf(award1.getUpload()),
				String.valueOf(award1.getTeaId()),
				String.valueOf(award1.getState()),
				
				String.valueOf(award1.getKind()),
				String.valueOf(award1.getUnitNum()),
				String.valueOf(award1.getEmpowerUnit()),
				String.valueOf(award2.getAwaId())
		};
		
		MysqlCrud mysqlcurd=new MysqlCrud();
		b=mysqlcurd.InsertAndUpdateData(sql, parameter);
		return b;
	}
	
	

	
	
	/**
	 * @category 对查询的所有获奖进行分页
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @return awards List<Award> 返回查询到的获奖列表
	 */
	public List<Award> getSegregateAwardByAll(int pageNow,int pageSize){
		String sql= "SELECT * FROM (select * from award order by state,kind,level,date desc) as A WHERE A.state>3 and A.awaId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Award> awards=new ArrayList<Award>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			awards.add(new MapToBean().MapToAward(map));
			i++;
		}
		return awards;
	}
	
	
	/**
	 * @category 根据名称对查询的所有获奖进行分页
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @param name String 获奖的名称
	 * @return awards List<Award> 返回查询到的获奖列表
	 */
	public List<Award> getSegregateAwardByName(String name,int pageNow,int pageSize){
		String sql= "SELECT * FROM (select * from award order by state,kind,level,date desc) as A WHERE A.state>3 and A.name like '%"+name+"%' and A.awaId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
				int i=0;
				List<Award> awards=new ArrayList<Award>();
				MysqlCrud mysqlcurd=new MysqlCrud();
				List<Map> list = mysqlcurd.SelectData(sql, null);
				//System.out.println(list.size());
				while(i<list.size()){
					Map map=list.get(i);
					awards.add(new MapToBean().MapToAward(map));
					i++;
				}
				return awards;
	}
	
	
	/**
	 * @category 根据日期对查询的所有获奖进行分页
	 * @param pageSize int 一个页面要显示pageSize条个数据
	 * @param pageNow int 从pageNow当前的数据来显示
	 * @param date1 Date 开始日期
	 * @param date2 Date 结束日期
	 * @return awards List<Award> 返回查询到的获奖列表
	 */
	public List<Award> getSegregateAwardByDate(Date date1,Date date2,int pageNow,int pageSize){
		String sql= "SELECT * FROM (select * from award order by state,kind,level,date desc) as A WHERE A.state>3 and A.date>='"+date1+"' and A.date<='"+date2+"' and A.awaId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
				int i=0;
				List<Award> awards=new ArrayList<Award>();
				MysqlCrud mysqlcurd=new MysqlCrud();
				List<Map> list = mysqlcurd.SelectData(sql, null);
				//System.out.println(list.size());
				while(i<list.size()){
					Map map=list.get(i);
					awards.add(new MapToBean().MapToAward(map));
					i++;
				}
				return awards;
	}
	
	
	/**
	 * @category 根据名称统计获奖的数量
	 * @return sum int 获奖的数量
	 */
		public int getCountAwardByName(String name)
		{
			String sql=null;
			int count=0;
			if(name.equals("")||name==null)
			{
				sql="select count(*) from award where state>3";
			}
			else
			{
				sql="select count(*) from award where state>3 and name like '%"+name+"%'";
			}
			
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @category 根据日期统计获奖的数量
		 * @return count int 获奖的数量
		 */
		public int getCountAwardByDate(Date date1,Date date2)
		{
			int count=0;
			String sql="select count(*) from award where state>3 and date>='"+date1+"' and date<='"+date2+"'";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @category 根据审核状态来统计获奖的数量
		 * @param state int 4表示未审核，5表示审核成功
		 * @return int
		 */
		public int getCountAwardByState(int state)
		{
			int count=0;
			String sql="select count(*) from award where state=?";
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据审核状态的不同来查询所有的获奖
		 * @param state 4表示未审核，5表示已经审核
		 * @return List
		 */
		public List<Award> getSegregateAwardByState(int state,int pageNow,int pageSize){
			String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
			int i=0;
			List<Award> awards=new ArrayList<Award>();
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(i<list.size()){
				Map map=list.get(i);
				awards.add(new MapToBean().MapToAward(map));
				i++;
			}
			return awards;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同的条件来查询获奖并进行分页,针对于特定的方向
		 * @return List
		 */
		public List<Award> getSegregateAwardByDirection(Award award,String date2,Integer direction,int pageNow,int pageSize,int state,List<String> orders){
//			//String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int j=0;
			String sql=null;
			String sql2=" and awaId LIMIT "+(pageNow-1)+","+pageSize+"";
			List<Award> awards=new ArrayList<Award>();
			if(orders.size()>0)
			{
				String sqlOrder="";
				for(int i=0;i<orders.size();i++)
				{
					if(i!=orders.size()-1)
					sqlOrder+=orders.get(i)+",";
					else
					sqlOrder+=orders.get(i);
					
				}
				sql="select * from (select * from award order by "+sqlOrder+") as aw,teacher as t where aw.teaId=t.teaId and t.direction=? and aw.state>'"+state+"'";
			}
			else
			sql="select * from  award  as aw , teacher as t where aw.teaId=t.teaId and t.direction=? and aw.state>'"+state+"'";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			//System.out.println("参数的尺寸为:"+paras.size());
			/*if(direction!=null)
			{
				System.out.println("fdsafdsafsda我进来了1");
				sql1.add(" A.teaId=T.teaId and T.direction=? ");
				paras.add(String.valueOf(direction));
				System.out.println("尺寸为:"+paras.size());
			}*/
			paras.add(String.valueOf(direction));
			if(award!=null)
			{
				if(award.getName()!=null)
				{
					sql1.add(" A.name like ? ");
					paras.add("%"+String.valueOf(award.getName())+"%");
				}
				if(award.getProjectName()!=null)
				{
					sql1.add(" A.projectName like ? ");
					paras.add("%"+String.valueOf(award.getProjectName())+"%");
				}
				if(award.getLevel()!=null)
				{
					sql1.add(" A.level=? ");
					paras.add(String.valueOf(award.getLevel()));
				}
				if(award.getPeople()!=null)
				{
					sql1.add(" A.people like ? ");
					paras.add("%"+String.valueOf(award.getPeople())+"%");
				}
				if(award.getDate()!=null)
				{
					sql1.add(" A.date>=? and A.date<=? ");
					paras.add(String.valueOf(award.getDate()));
					paras.add(date2);
					
				}
				if(award.getKind()!=null)
				{
					sql1.add(" A.kind=? ");
					paras.add(String.valueOf(award.getKind()));
				}
				if(award.getEmpowerUnit()!=null)
				{
					sql1.add(" A.empowerUnit=? ");
					paras.add(String.valueOf(award.getEmpowerUnit()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
					sql+="and"+sql1.get(i);
			}
			
			//加入最后一个,该字符是用于分页
			sql+=sql2;
		//	System.out.println("yuju"+sql);
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				awards.add(new MapToBean().MapToAward(map));
				j++;
			}
			return awards;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同的条件来查询获奖,针对于特定的方向
		 * @return List
		 */
		public List<Award> getAwardByDirection(Award award,String date2,int direction,int state){
//			//String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int j=0;
			List<Award> awards=new ArrayList<Award>();
			String sql="select * from award as A , teacher as T where A.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			System.out.println("参数的尺寸为:"+paras.size());
			if(award!=null)
			{
				if(award.getName()!=null)
				{
					sql1.add(" A.name like ? ");
					paras.add("%"+String.valueOf(award.getName())+"%");
				}
				if(award.getProjectName()!=null)
				{
					sql1.add(" A.projectName like ? ");
					paras.add("%"+String.valueOf(award.getProjectName())+"%");
				}
				if(award.getLevel()!=null)
				{
					sql1.add(" A.level=? ");
					paras.add(String.valueOf(award.getLevel()));
				}
				if(award.getPeople()!=null)
				{
					sql1.add(" A.people like ? ");
					paras.add("%"+String.valueOf(award.getPeople())+"%");
				}
				if(award.getDate()!=null)
				{
					sql1.add(" A.date>=? and A.date<=? ");
					paras.add(String.valueOf(award.getDate()));
					paras.add(date2);
					
				}
				if(award.getKind()!=null)
				{
					sql1.add(" A.kind=? ");
					paras.add(String.valueOf(award.getKind()));
				}
				if(award.getEmpowerUnit()!=null)
				{
					sql1.add(" A.empowerUnit=? ");
					paras.add(String.valueOf(award.getEmpowerUnit()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}	
			
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				awards.add(new MapToBean().MapToAward(map));
				j++;
			}
			return awards;
		}
		
		/**
		 * @author yaojun
		 * @category 根据不同的条件来查询所有的已经审核通过的获奖
		 * @return List
		 */
		public List<Award> getAwardByAll(Award award,String date2,int direction,int state){
//			//String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int j=0;
			List<Award> awards=new ArrayList<Award>();
			String sql="select * from award as A , teacher as T where A.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));

			System.out.println("参数1的尺寸为:"+paras.size());
			if(award!=null)
			{
				if(award.getName()!=null)
				{
					sql1.add(" A.name like ? ");
					paras.add("%"+String.valueOf(award.getName())+"%");
				}
				if(award.getProjectName()!=null)
				{
					sql1.add(" A.projectName like ? ");
					paras.add("%"+String.valueOf(award.getProjectName())+"%");
				}
				if(award.getLevel()!=null)
				{
					sql1.add(" A.level=? ");
					paras.add(String.valueOf(award.getLevel()));
				}
				if(award.getPeople()!=null)
				{
					sql1.add(" A.people like ? ");
					paras.add("%"+String.valueOf(award.getPeople())+"%");
				}
				if(award.getDate()!=null)
				{
					sql1.add(" A.date>=? and A.date<=? ");
					paras.add(String.valueOf(award.getDate()));
					paras.add(date2);
					
				}
				if(award.getKind()!=null)
				{
					sql1.add(" A.kind=? ");
					paras.add(String.valueOf(award.getKind()));
				}
				if(award.getEmpowerUnit()!=null)
				{
					sql1.add(" A.empowerUnit=? ");
					paras.add(String.valueOf(award.getEmpowerUnit()));
				}
			}
			System.out.println("参数2的尺寸为:"+paras.size());
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}	
			
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			while(j<list.size()){
				Map map=list.get(j);
				awards.add(new MapToBean().MapToAward(map));
				j++;
			}
			return awards;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同的条件来统计过奖的数量,针对于特定的方向
		 * @return int
		 */
		public int getAwardCountByDirection(Award award,String date2,int direction,int state){
//			//String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int count=0;
			String sql="select count(*) from award as A , teacher as T where A.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(award!=null)
			{
				if(award.getName()!=null)
				{
					sql1.add(" A.name like ? ");
					paras.add("%"+String.valueOf(award.getName())+"%");
				}
				if(award.getProjectName()!=null)
				{
					sql1.add(" A.projectName like ? ");
					paras.add("%"+String.valueOf(award.getProjectName())+"%");
				}
				if(award.getLevel()!=null)
				{
					sql1.add(" A.level=? ");
					paras.add(String.valueOf(award.getLevel()));
				}
				if(award.getPeople()!=null)
				{
					sql1.add(" A.people like ? ");
					paras.add("%"+String.valueOf(award.getPeople())+"%");
				}
				if(award.getDate()!=null)
				{
					sql1.add(" A.date>=? and A.date<=? ");
					paras.add(String.valueOf(award.getDate()));
					paras.add(date2);
					
				}
				if(award.getKind()!=null)
				{
					sql1.add(" A.kind=? ");
					paras.add(String.valueOf(award.getKind()));
				}
				if(award.getEmpowerUnit()!=null)
				{
					sql1.add(" A.empowerUnit=? ");
					paras.add(String.valueOf(award.getEmpowerUnit()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		/**
		 * @author yaojun
		 * @category 根据不同的条件来查询获奖,针对于特定的教师工号
		 * @return List
		 */
		public List<Award> getAwardByUser(Award award,String date2,Teacher teacher){
//			//String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int j=0;
			List<Award> awards=new ArrayList<Award>();
			String sql="select * from award as A , teacher as T where A.teaId=T.teaId and T.user=? ";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(teacher.getUser());
			System.out.println("参数的尺寸为:"+paras.size());
			if(award!=null)
			{
				if(award.getName()!=null)
				{
					sql1.add(" A.name like ? ");
					paras.add("%"+String.valueOf(award.getName())+"%");
				}
				if(award.getProjectName()!=null)
				{
					sql1.add(" A.projectName like ? ");
					paras.add("%"+String.valueOf(award.getProjectName())+"%");
				}
				if(award.getLevel()!=null)
				{
					sql1.add(" A.level=? ");
					paras.add(String.valueOf(award.getLevel()));
				}
				if(award.getPeople()!=null)
				{
					sql1.add(" A.people like ? ");
					paras.add("%"+String.valueOf(award.getPeople())+"%");
				}
				if(award.getDate()!=null)
				{
					sql1.add(" A.date>=? and A.date<=? ");
					paras.add(String.valueOf(award.getDate()));
					paras.add(date2);
					
				}
				if(award.getKind()!=null)
				{
					sql1.add(" A.kind=? ");
					paras.add(String.valueOf(award.getKind()));
				}
				if(award.getEmpowerUnit()!=null)
				{
					sql1.add(" A.empowerUnit=? ");
					paras.add(String.valueOf(award.getEmpowerUnit()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				awards.add(new MapToBean().MapToAward(map));
				j++;
			}
			return awards;
		}
		
		
		/**
		 * @author yaojun
		 * @category 根据不同的条件来查询获奖,针对于特定的教师工号
		 * @return List
		 */
		public int getAwardCountByUser(Award award,String date2,Teacher teacher){
//			//String sql= "SELECT * FROM award WHERE state=? and awaId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			int count=0;
			List<Award> awards=new ArrayList<Award>();
			String sql="select count(*) from award as A , teacher as T where A.teaId=T.teaId and T.user=? ";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(teacher.getUser());
			System.out.println("参数的尺寸为:"+paras.size());
			if(award!=null)
			{
				if(award.getName()!=null)
				{
					sql1.add(" A.name like ? ");
					paras.add("%"+String.valueOf(award.getName())+"%");
				}
				if(award.getProjectName()!=null)
				{
					sql1.add(" A.projectName like ? ");
					paras.add("%"+String.valueOf(award.getProjectName())+"%");
				}
				if(award.getLevel()!=null)
				{
					sql1.add(" A.level=? ");
					paras.add(String.valueOf(award.getLevel()));
				}
				if(award.getPeople()!=null)
				{
					sql1.add(" A.people like ? ");
					paras.add("%"+String.valueOf(award.getPeople())+"%");
				}
				if(award.getDate()!=null)
				{
					sql1.add(" A.date>=? and A.date<=? ");
					paras.add(String.valueOf(award.getDate()));
					paras.add(date2);
					
				}
				if(award.getKind()!=null)
				{
					sql1.add(" A.kind=? ");
					paras.add(String.valueOf(award.getKind()));
				}
				if(award.getEmpowerUnit()!=null)
				{
					sql1.add(" A.empowerUnit=? ");
					paras.add(String.valueOf(award.getEmpowerUnit()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		
}
